using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public static partial class LinkInfoService
    {
        public static bool AddLinkInfo(LinkInfo linkInfo)
        {
            string sql =
                "INSERT LinkInfo (Title, Location, Image, TypeID)" +
                "VALUES (@Title, @Location, @Image, @TypeID)";

            sql += " ; SELECT @@IDENTITY";


            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@Title", linkInfo.Title),
                new SqlParameter("@Location", linkInfo.Location),
				new SqlParameter("@Image", linkInfo.Image),
				new SqlParameter("@TypeID", linkInfo.TypeID)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }


        public static bool DeleteLinkInfoByID(int iD)
        {
            string sql = "DELETE LinkInfo WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        public static bool ModifyLinkInfo(LinkInfo linkInfo)
        {
            string sql =
                "UPDATE LinkInfo " +
                "SET " +
                    "Title = @Title, " +
                    "Location = @Location, " +
                    "Image = @Image, " +
                    "TypeID = @TypeID " +
                "WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", linkInfo.ID),
				new SqlParameter("@Title", linkInfo.Title),
                new SqlParameter("@Location", linkInfo.Location),
				new SqlParameter("@Image", linkInfo.Image),
				new SqlParameter("@TypeID", linkInfo.TypeID)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static IList<LinkInfo> GetAllLinkInfos()
        {
            string sqlAll = "SELECT * FROM LinkInfo";
            return GetLinkInfosBySql(sqlAll);
        }

        public static IList<LinkInfo> GetLinkInfosByTypeID(int typeID)
        {
            string sqlAll = "SELECT * FROM LinkInfo where TypeID=@TypeID";
            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@TypeID", typeID)
			};
            return GetLinkInfosBySql(sqlAll, para);
        }

        public static LinkInfo GetLinkInfoByID(int iD)
        {
            string sql = "SELECT * FROM LinkInfo WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            LinkInfo linkInfo = new LinkInfo();

            foreach (DataRow dr in temp.Rows)
            {
                linkInfo.ID = (int)dr["ID"];
                linkInfo.Title = (string)dr["Title"];
                linkInfo.Location = (string)dr["Location"];
                linkInfo.Image = (string)dr["Image"];
                linkInfo.TypeID = (int)dr["TypeID"];

            }
            return linkInfo;

        }


        private static IList<LinkInfo> GetLinkInfosBySql(string safeSql)
        {
            List<LinkInfo> list = new List<LinkInfo>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, null))
            {
                while (dr.Read())
                {
                    LinkInfo linkInfo = new LinkInfo();

                    linkInfo.ID = (int)dr["ID"];
                    linkInfo.Title = (string)dr["Title"];
                    linkInfo.Location = (string)dr["Location"];
                    linkInfo.Image = (string)dr["Image"];
                    linkInfo.TypeID = (int)dr["TypeID"];

                    list.Add(linkInfo);
                }
            }
            return list;
        }

        private static IList<LinkInfo> GetLinkInfosBySql(string safeSql, params SqlParameter[] para)
        {
            List<LinkInfo> list = new List<LinkInfo>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, para))
            {
                while (dr.Read())
                {
                    LinkInfo linkInfo = new LinkInfo();

                    linkInfo.ID = (int)dr["ID"];
                    linkInfo.Title = (string)dr["Title"];
                    linkInfo.Location = (string)dr["Location"];
                    linkInfo.Image = (string)dr["Image"];
                    linkInfo.TypeID = (int)dr["TypeID"];

                    list.Add(linkInfo);
                }
            }
            return list;
        }
    }
}
